#!/usr/bin/env bash
dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -udashuju -pDashuju2017 -h192.168.0.202 -P31006 <<EOF
use wechatmall;
select from_unixtime(A.create_time,'%Y-%m-%d')as day,B.station_name,
sum(case when A.store_id = 1 and A.status = -1 THEN weight ELSE 0 END) AS refunded_number,
sum(case when A.store_id = 1 and A.status = -1 THEN amount_point ELSE 0 END) AS refunded_point,
sum(case when A.store_id = 1 and A.status = 0 THEN weight ELSE 0 END) AS notpay_number,
sum(case when A.store_id = 1 and A.status = 0 THEN amount_point ELSE 0 END) AS notpay_point,
sum(case when A.store_id = 1 and A.status = 2 THEN weight ELSE 0 END) AS tosend_number,
sum(case when A.store_id = 1 and A.status = 2 THEN amount_point ELSE 0 END) AS tosend_point,
sum(case when A.store_id = 1 and A.status = 7 THEN weight ELSE 0 END) AS paid_number,
sum(case when A.store_id = 1 and A.status = 7 THEN amount_point ELSE 0 END) AS paid_point,
sum(case when A.store_id = 1 and A.status = 8 THEN weight ELSE 0 END) AS cancel_number,
sum(case when A.store_id = 1 and A.status = 8 THEN amount_point ELSE 0 END) AS cancel_point
from es_order as A
join es_member_store_child as B
on A.child_store_id =  B.child_store_id
where from_unixtime(A.create_time,'%Y-%m-%d')='$dt'
group by from_unixtime(A.create_time,'%Y-%m-%d'),B.station_name;
EOF